# -*- coding: utf-8 -*-

import wx
import sqlparse
import time
import wx.lib.mixins.listctrl as listmix

TABVIEW_SELECTION_SINGLE_ROW = 0
TABVIEW_SELECTION_OTHER_STATEMENT = 1

class TableData:
    def __init__(self, colNames, colValues, tableName):
        self.tableName = tableName
        self.colNames = []
        self.listOfColValues = []
        
        self.colNames = colNames
        self.Add(colValues)        
        
    def Add(self, colValues):
        self.listOfColValues.append(colValues)
        
class Parser:
    def __init__(self):
        self.Reset()
        
    def ParseSqlElem(self, tokenList, sqlIdentifiers):
        if not isinstance(tokenList, sqlparse.sql.TokenList):
            raise TypeError('bad operand type')
         
        tFirstToken = tokenList.token_first()
        tNextToken = tokenList.token_next(tFirstToken)
        if tNextToken is None:
            return False
         
        tokenList.flatten()
        tToken = tokenList.token_first()
        while tToken is not None:
            if isinstance(tToken, sqlparse.sql.IdentifierList):
                sqlIdentifiers.append(tToken)
            if tToken.is_group():
                self.ParseSqlElem(tToken, sqlIdentifiers)
             
            tToken = tokenList.token_next(tToken)
        
        return True

    def ClassifyTable(self, colNames, colValues, tableName):
        if not isinstance(colNames, list) or not isinstance(colValues, list):            
            raise TypeError('bad operand type')
        
        if len(colNames) != len(colValues):
            print '[len(colNames)]', len(colNames), '[len(values)]', len(colValues)
            print "[Error]列名和列值不匹配"
            return
            
        t_listExistedTableNames = [x.tableName for x in self.listOfTableDatas]
        
        if tableName not in t_listExistedTableNames:
            newTableData = TableData(colNames, colValues, tableName)
            self.listOfTableDatas.append(newTableData)
        else:
            r_tableData = self.listOfTableDatas[t_listExistedTableNames.index(tableName)]
            r_tableData.Add(colValues)

    def ExtractTableName(self, sql, beforeStr, behindStr):
        beforeIdx = sql.find(beforeStr) + len(beforeStr)
        behindIdx = sql.find(behindStr)
        return sql[beforeIdx:behindIdx]
    
    def Reset(self):   
        self.listOfTableDatas = []
        self.otherStatements = []
        
    
    def Parse(self, event, insertSql):     
        self.Reset()
        
        self.insertSql = sqlparse.format(insertSql, strip_comments=True)
        
        parsedTuple = sqlparse.parse(self.insertSql)
          
        for parsed in parsedTuple:            
            sqlIdentifierLists = []
            if not self.ParseSqlElem(parsed, sqlIdentifierLists):
                continue
          
            if parsed.get_type() != 'INSERT':
                statementFakeList = []
                statementFakeList.append(parsed.to_unicode())
                self.otherStatements.append(statementFakeList)
                continue
            
            tableName = self.ExtractTableName(parsed.to_unicode().lower(), "insert into", "(").strip()
            self.ClassifyTable([x.to_unicode().strip('\'') for x in sqlIdentifierLists[0].get_identifiers()],
                                [x.to_unicode().strip('\'') for x in sqlIdentifierLists[1].get_identifiers()],
                                tableName)  # 分类
    
        return self.listOfTableDatas, self.otherStatements

class SortableListCtrl(wx.ListCtrl, listmix.ColumnSorterMixin):
    def __init__(self, parent, colcnt):
        wx.ListCtrl.__init__(self, parent, -1, style=wx.LC_REPORT)
                
        self.itemDataMap = {}
        
        listmix.ColumnSorterMixin.__init__(self, colcnt)
    
    def GetListCtrl(self):
        return self
    
    def SetItemDataMap(self, itemDataMap):
        self.itemDataMap = itemDataMap

class MainFrame(wx.Frame):
    def __init__(self, parent, title):
        super(MainFrame, self).__init__(parent, title=title, size=(800, 600))
        
        self.parser = Parser()
         
        self.InitTextCtrl()
        self.InitButton()
        self.InitGauge()
        self.InitMultiView()
        
        self.LayoutHorizontalCtrls()
        self.LayoutVerticalCtrls()
        
        self.Show()
        self.Center()

        
    def InitListhead(self, r_listCtrl, r_colNames):
        [r_listCtrl.InsertColumn(colIdx, colName) for colIdx, colName in 
            zip(range(len(r_colNames)), r_colNames)]
        
        self.FitColWidth(r_listCtrl)
        
    def FitColWidth(self, r_listCtrl):
        [r_listCtrl.SetColumnWidth(colIdx, wx.LIST_AUTOSIZE_USEHEADER) 
            for colIdx in range(r_listCtrl.GetColumnCount())]
            
    def FillList(self, r_listCtrl, r_contents):
        for rowIndex in range(len(r_contents)):
            contentRow = r_contents[rowIndex]
            
            for colIndex in range(len(contentRow)):
                if colIndex == 0:
                    r_listCtrl.InsertStringItem(rowIndex, contentRow[colIndex])
                else:
                    r_listCtrl.SetStringItem(rowIndex, colIndex, contentRow[colIndex].strip())
            
            r_listCtrl.SetItemData(rowIndex, rowIndex)
                
        self.FitColWidth(r_listCtrl)
        
    def CreateViewOtherStatements(self, otherStatements):
        if len(otherStatements) != 0:
            t_list = wx.ListCtrl(self.tabViewRight, -1, style=wx.LC_REPORT)
            t_list.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnListItemSelectedOtherStatements)
            self.tabViewRight.AddPage(t_list, 'other statements')
            
            self.InitListhead(t_list, ['index', 'statement'])
            self.FillList(t_list, self.PrependIndex2ListData(otherStatements))
            
    def OnListItemSelectedOtherStatements(self, event):
        print int(event.GetData()), self.tabViewRight.GetCurrentPage().GetItemData(event.m_itemIndex)
        self.tabViewLeft.SetSelection(TABVIEW_SELECTION_OTHER_STATEMENT)
        self.displayTextCtrl.Clear()
        for x in self.otherStatements[int(event.GetData())]:
            self.displayTextCtrl.WriteText(x)
        
            
    def GenerateIndexStrList(self, r_list):
        return [str(x + 1) for x in range(len(r_list))]
    
    def PrependIndex2ListData(self, r_dataRows):
        return [[indexStr] + row for indexStr, row in zip(self.GenerateIndexStrList(r_dataRows), r_dataRows)]
            
    def ResetCtrlsWhenButtonAnalyse(self):
        self.list.DeleteAllItems()
        self.tabViewRight.DeleteAllPages()
        self.displayTextCtrl.Clear()
        
    def OnButtonAnalyse(self, event):
        if self.editCtrl.GetValue().strip('\n\t ') == '':
            wx.MessageBox('Invalid insert sql')
            return
        
        self.gauge.SetValue(0)
        
        self.ResetCtrlsWhenButtonAnalyse()
        
        self.listOfTableDatas, self.otherStatements = self.parser.Parse(event, self.editCtrl.GetValue())
        
        self.gauge.SetValue(50)
        
        if len(self.listOfTableDatas) == 0 and len(self.otherStatements) == 0:
            return
        
        total = len(self.listOfTableDatas)
        progressIdx = 0
        for tableData in self.listOfTableDatas:
            progressIdx += 1.0
            
            t_list = SortableListCtrl(self.tabViewRight, 1 + len(tableData.colNames))
            t_list.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnListItemSelected)
            self.tabViewRight.AddPage(t_list, tableData.tableName)
             
            self.InitListhead(t_list, ['index'] + tableData.colNames)
            
            t_dataRows = self.PrependIndex2ListData(tableData.listOfColValues)
            self.FillList(t_list, t_dataRows)
            
            t_dictForListCtrlSort = {}
            for index in range(len(tableData.listOfColValues)):
                t_dictForListCtrlSort[index] = [index, ] + tableData.listOfColValues[index]
            
            t_list.SetItemDataMap(t_dictForListCtrlSort)
            
            self.gauge.SetValue(progressIdx / total * 50 + 50)
            self.gauge.Refresh()
            
            self.Refresh()
            
        self.CreateViewOtherStatements(self.otherStatements)
        
        self.gauge.SetValue(100)
               
        self.Refresh()
        
        print 'done'
    
    def OnListItemSelected(self, event):
        print int(event.GetData())
        self.tabViewLeft.SetSelection(TABVIEW_SELECTION_SINGLE_ROW)
        # 清除list内容
        # 获取点击的语句内容
        # 设置list内容
        self.list.DeleteAllItems()
        t_tableData = self.listOfTableDatas[self.tabViewRight.GetSelection()]
        t_colNames = t_tableData.colNames
        t_values = t_tableData.listOfColValues[int(event.GetData())]
        
        self.FillList(self.list,
                       self.PrependIndex2ListData([[colname, val] for colname, val in zip(t_colNames, t_values)]))
        
        
    def InitTextCtrl(self):
        self.editCtrl = wx.TextCtrl(self, size=(self.GetSize().x, 150),
                                    style=wx.TE_MULTILINE | wx.TE_PROCESS_ENTER | wx.TE_RICH)
        self.editCtrl.SetMaxLength(0)
        
    def InitButton(self):
        self.buttonAnalyse = wx.Button(self, label='Analyse')
        self.buttonAnalyse.Bind(wx.EVT_BUTTON, self.OnButtonAnalyse)
        
    def InitGauge(self):
        self.gauge = wx.Gauge(self)
        self.gauge.SetValue(0)
        
    def LayoutHorizontalCtrls(self):
        self.hbox = wx.BoxSizer(wx.HORIZONTAL)
        self.hbox.Add(self.buttonAnalyse, flag=wx.EXPAND | wx.ALL, border=0)
        self.hbox.Add((20, wx.SIZE_AUTO_HEIGHT))
        self.hbox.Add(self.gauge, flag=wx.EXPAND | wx.ALL, border=0)
        
    def LayoutVerticalCtrls(self):
        self.vbox = wx.BoxSizer(wx.VERTICAL)
        self.SetSizer(self.vbox)
        self.vbox.Add(self.editCtrl, flag=wx.EXPAND | wx.TOP | wx.BOTTOM, border=0)
        self.vbox.Add(self.hbox, flag=wx.EXPAND)
        self.vbox.Add(self.multiView, flag=wx.EXPAND | wx.TOP | wx.BOTTOM, border=0)
        
    def InitMultiView(self):
        self.multiView = wx.SplitterWindow(self, size=(self.GetSize().x, 600), style=wx.SP_LIVE_UPDATE)
        
        self.InitChildViewTabView()
        self.InitChildViewList()
        self.InitDisplayTextCtrl()
        
        self.tabViewLeft.AddPage(self.list, 'single row')
        self.tabViewLeft.AddPage(self.displayTextCtrl, 'other statement')
        
        self.multiView.SplitVertically(self.tabViewLeft, self.tabViewRight)
        self.multiView.SetMinimumPaneSize(200)
        
    def InitDisplayTextCtrl(self):
        self.displayTextCtrl = wx.TextCtrl(self.tabViewLeft,
                                           style=wx.TE_MULTILINE | wx.TE_PROCESS_ENTER 
                                           | wx.TE_RICH | wx.TE_READONLY)
        self.displayTextCtrl.SetMaxLength(0)
        
    
    def InitChildViewList(self):
        self.list = wx.ListCtrl(self.tabViewLeft, -1, style=wx.LC_REPORT)         
        self.InitListhead(self.list, ['index', 'colume name', 'value'])
 
    def InitChildViewTabView(self):
        self.tabViewRight = self.GenerateNotebookInMultiview()
        self.tabViewLeft = self.GenerateNotebookInMultiview()
        
    def GenerateNotebookInMultiview(self):
        return wx.Notebook(self.multiView, -1,
                           size=(wx.SIZE_AUTO_WIDTH, wx.SIZE_AUTO_HEIGHT),
                           style=wx.NB_MULTILINE)
         
def main():
    app = wx.App()
    MainFrame(None, title='Sql analyser')
    app.MainLoop()
    
if __name__ == '__main__':  
    main()
